﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace CNotebook
{
    public class BankTrsfrManager:ConnectionManager
    {
        #region Add/Updating
        public SqlException AddNewBankTransfer(BankTrsfrUnit oUnit)
        {
            SqlCommand oCommand = new SqlCommand();

            try
            {
                oCommand.Connection = this.Connection;
                oCommand.CommandText = "INSERT INTO FUNDTRANSFER_RECEIPT (DocNum, Reference, Remarks, DocType, ReceiptNumber, PostingDate, TINNumber, IsAccount, PayeeCode, PayeeName, AddressOrRemarks, BankName, RefNumber, TransferDate, TrsfrAmount, DocStatus, DateCreated, CreatedBy, DateModified, ModifiedBy) VALUES (@DocNum, @Reference, @Remarks, @DocType, @ReceiptNumber, @PostingDate, @TINNumber, @IsAccount, @PayeeCode, @PayeeName, @AddressOrRemarks, @BankName, @RefNumber, @TransferDate, @TrsfrAmount, @DocStatus, @DateCreated, @CreatedBy, @DateModified, @ModifiedBy)";

                oCommand.Parameters.Add(new SqlParameter("@DocNum", oUnit.DocumentNumber));
                oCommand.Parameters.Add(new SqlParameter("@Remarks", oUnit.Remarks));
                oCommand.Parameters.Add(new SqlParameter("@Reference", oUnit.Reference));
                oCommand.Parameters.Add(new SqlParameter("@DocType", oUnit.DocumentType));
                oCommand.Parameters.Add(new SqlParameter("@ReceiptNumber", oUnit.ReceiptNumber));
                oCommand.Parameters.Add(new SqlParameter("@PostingDate", oUnit.PostingDate));
                oCommand.Parameters.Add(new SqlParameter("@TINNumber", oUnit.TINNumber));
                oCommand.Parameters.Add(new SqlParameter("@IsAccount", oUnit.IsAccount));
                oCommand.Parameters.Add(new SqlParameter("@PayeeCode", oUnit.CardCode));
                oCommand.Parameters.Add(new SqlParameter("@PayeeName", oUnit.CardName));
                oCommand.Parameters.Add(new SqlParameter("@AddressOrRemarks", oUnit.Address));

                oCommand.Parameters.Add(new SqlParameter("@BankName", oUnit.BankName));
                oCommand.Parameters.Add(new SqlParameter("@RefNumber", oUnit.RefNumber));
                oCommand.Parameters.Add(new SqlParameter("@TransferDate", oUnit.TransferDate));
                oCommand.Parameters.Add(new SqlParameter("@TrsfrAmount", oUnit.TransferAmount));

                //RefNumber, TransferDate, TrsfrAmount, CheckStatus,
                oCommand.Parameters.Add(new SqlParameter("@DocStatus", oUnit.DocumentStatus));
                oCommand.Parameters.Add(new SqlParameter("@DateCreated", oUnit.DateCreated));
                oCommand.Parameters.Add(new SqlParameter("@CreatedBy", oUnit.CreatedBy));
                oCommand.Parameters.Add(new SqlParameter("@DateModified", oUnit.DateModified));
                oCommand.Parameters.Add(new SqlParameter("@ModifiedBy", oUnit.ModifiedBy));

                oCommand.ExecuteNonQuery();
                return null;
            }
            catch (SqlException ex)
            {
                return ex;
            }
            finally
            {
                oCommand.Dispose();
            }
        }

        public SqlException UpdateBankTransfer(BankTrsfrUnit oUnit)
        {
            SqlCommand oCommand = new SqlCommand();

            try
            {
                oCommand.Connection = this.Connection;
                oCommand.CommandText = "UPDATE FUNDTRANSFER_RECEIPT SET Reference=@Reference, Remarks=@Remarks, DocType=@DocType, ReceiptNumber=@ReceiptNumber, PostingDate=@PostingDate, TINNumber=@TINNumber, IsAccount=@IsAccount, PayeeCode=@PayeeCode, PayeeName=@PayeeName, AddressOrRemarks=@AddressOrRemarks, BankName=@BankName, RefNumber=@RefNumber, TransferDate=@TransferDate, TrsfrAmount=@TrsfrAmount, DocStatus=@DocStatus, DateModified=@DateModified, ModifiedBy=@ModifiedBy WHERE DocNum=@DocNum";

                oCommand.Parameters.Add(new SqlParameter("@DocNum", oUnit.DocumentNumber));
                oCommand.Parameters.Add(new SqlParameter("@Reference", oUnit.Reference));
                oCommand.Parameters.Add(new SqlParameter("@Remarks", oUnit.Remarks));
                oCommand.Parameters.Add(new SqlParameter("@DocType", oUnit.DocumentType));
                oCommand.Parameters.Add(new SqlParameter("@ReceiptNumber", oUnit.ReceiptNumber));
                oCommand.Parameters.Add(new SqlParameter("@PostingDate", oUnit.PostingDate));
                oCommand.Parameters.Add(new SqlParameter("@TINNumber", oUnit.TINNumber));
                oCommand.Parameters.Add(new SqlParameter("@IsAccount", oUnit.IsAccount));
                oCommand.Parameters.Add(new SqlParameter("@PayeeCode", oUnit.CardCode));
                oCommand.Parameters.Add(new SqlParameter("@PayeeName", oUnit.CardName));
                oCommand.Parameters.Add(new SqlParameter("@AddressOrRemarks", oUnit.Address));

                oCommand.Parameters.Add(new SqlParameter("@BankName", oUnit.BankName));
                oCommand.Parameters.Add(new SqlParameter("@RefNumber", oUnit.RefNumber));
                oCommand.Parameters.Add(new SqlParameter("@TransferDate", oUnit.TransferDate));
                oCommand.Parameters.Add(new SqlParameter("@TrsfrAmount", oUnit.TransferAmount));


                oCommand.Parameters.Add(new SqlParameter("@DocStatus", oUnit.DocumentStatus));
                oCommand.Parameters.Add(new SqlParameter("@DateModified", oUnit.DateModified));
                oCommand.Parameters.Add(new SqlParameter("@ModifiedBy", oUnit.ModifiedBy));

                oCommand.ExecuteNonQuery();
                return null;
            }
            catch (SqlException ex)
            {
                return ex;
            }
            finally
            {
                oCommand.Dispose();
            }
        }
        #endregion

        #region Retreiving Data
        public DataTable GetBankTransfer(string DocNum)
        {
            DataTable dt = new DataTable();
            SqlCommand oCommand = new SqlCommand();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT * FROM FUNDTRANSFER_RECEIPT WHERE DocNum=@DocNum";
            oCommand.Parameters.Add(new SqlParameter("@DocNum", DocNum));

            SqlDataAdapter oAdapter = new SqlDataAdapter();
            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(dt);

            return dt;
        }

        public DataTable BrowseBankTransferByDocument(string _DocNum)
        {
            DataTable dt = new DataTable();
            SqlCommand oCommand = new SqlCommand();

            _DocNum = '%' + _DocNum.Trim() + '%';

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT * FROM FUNDTRANSFER_RECEIPT WHERE DocNum LIKE @DocNum";
            oCommand.Parameters.Add(new SqlParameter("@DocNum", _DocNum));


            SqlDataAdapter oAdapter = new SqlDataAdapter();

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(dt);

            return dt;
        }

        public DataTable BrowseBankTransferByReceiptNumner(string _DocumentType, string _ReceiptNumber)
        {
            DataTable dt = new DataTable();
            SqlCommand oCommand = new SqlCommand();

            _ReceiptNumber = '%' + _ReceiptNumber.Trim() + '%';

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT * FROM FUNDTRANSFER_RECEIPT WHERE DocType=@DocType AND ReceiptNumber LIKE @ReceiptNumber";
            oCommand.Parameters.Add(new SqlParameter("@ReceiptNumber", _ReceiptNumber));
            oCommand.Parameters.Add(new SqlParameter("@DocType", _DocumentType));

            SqlDataAdapter oAdapter = new SqlDataAdapter();
            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(dt);

            return dt;
        }

        public DataTable BrowseBankTransferByCardName(string _CardName)
        {
            DataTable dt = new DataTable();
            SqlCommand oCommand = new SqlCommand();

            _CardName = '%' + _CardName.Trim() + '%';

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT * FROM FUNDTRANSFER_RECEIPT WHERE CardName LIKE @CardName";
            oCommand.Parameters.Add(new SqlParameter("@CardName", _CardName));

            SqlDataAdapter oAdapter = new SqlDataAdapter();
            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(dt);

            return dt;
        }

        public DataTable BrowseBankTransferByRemarks(string _Remarks)
        {
            DataTable dt = new DataTable();
            SqlCommand oCommand = new SqlCommand();

            _Remarks = '%' + _Remarks.Trim() + '%';

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT * FROM FUNDTRANSFER_RECEIPT WHERE Remarks LIKE @Remarks";
            oCommand.Parameters.Add(new SqlParameter("@Remarks", _Remarks));

            SqlDataAdapter oAdapter = new SqlDataAdapter();
            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(dt);

            return dt;
        }

        public void PostDocument(string _DOCUMENT_NUMBER)
        {
            SqlCommand oCommand = new SqlCommand();

            oCommand.Connection = this.Connection;

            oCommand.CommandText = "UPDATE FUNDTRANSFER_RECEIPT SET DOCSTATUS='Posted' WHERE DocNum=@DocNum";
            oCommand.Parameters.Add(new SqlParameter("@DocNum", _DOCUMENT_NUMBER));
            oCommand.ExecuteNonQuery();

        }

        public void CloseDocument(string _DOCUMENT_NUMBER)
        {
            SqlCommand oCommand = new SqlCommand();

            oCommand.Connection = this.Connection;

            oCommand.CommandText = "UPDATE FUNDTRANSFER_RECEIPT SET DOCSTATUS='Closed' WHERE DocNum=@DocNum";
            oCommand.Parameters.Add(new SqlParameter("@DocNum", _DOCUMENT_NUMBER));
            oCommand.ExecuteNonQuery();
        }

        public void CancelDocument(string _DOCUMENT_NUMBER)
        {
            SqlCommand oCommand = new SqlCommand();

            oCommand.Connection = this.Connection;

            oCommand.CommandText = "UPDATE FUNDTRANSFER_RECEIPT SET DOCSTATUS='Canceled' WHERE DocNum=@DocNum";
            oCommand.Parameters.Add(new SqlParameter("@DocNum", _DOCUMENT_NUMBER));
            oCommand.ExecuteNonQuery();
        }
        #endregion
    }
}
